--    Program: 		CAPPOIMP_PURCHASE_ORDER_LINE.sql
--    Description:  Import PO Lines All information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         23-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp    		varchar2(2000);
   
   l_po_line_id				number;
   l_item_id				number;
                    		                 		
   cursor cur_lines_all is
      select * from CRST_PO_LINES_ALL for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_lines_all loop   	
     l_err_msg_tmp := '';
     
     select max(inventory_item_id_n)
     into   l_item_id
     from   CRST_MTL_SYSTEM_ITEMS_B
     where  inventory_item_id = cur.ITEM_ID;
     
     select PO_LINES_S.NEXTVAL 
     INTO l_po_line_id
     FROM DUAL;
     
     update CRST_PO_LINES_ALL
     set    po_line_id_n = l_po_line_id
     where  po_line_id = cur.PO_LINE_ID;
     
     begin
       -- import data
       insert into PO_LINES_ALL 
       (
                PO_LINE_ID                   ,
                LAST_UPDATE_DATE             ,
                LAST_UPDATED_BY              ,
                PO_HEADER_ID                 ,
                LINE_TYPE_ID                 ,
                LINE_NUM                     ,
                LAST_UPDATE_LOGIN            ,
                CREATION_DATE                ,
                CREATED_BY                   ,
                ITEM_ID                      ,
                ITEM_REVISION                ,
                CATEGORY_ID                  ,
                ITEM_DESCRIPTION             ,
                UNIT_MEAS_LOOKUP_CODE        ,
                QUANTITY_COMMITTED           ,
                COMMITTED_AMOUNT             ,
                ALLOW_PRICE_OVERRIDE_FLAG    ,
                NOT_TO_EXCEED_PRICE          ,
                LIST_PRICE_PER_UNIT          ,
                UNIT_PRICE                   ,
                QUANTITY                     ,
                UN_NUMBER_ID                 ,
                HAZARD_CLASS_ID              ,
                NOTE_TO_VENDOR               ,
                FROM_HEADER_ID               ,
                FROM_LINE_ID                 ,
                MIN_ORDER_QUANTITY           ,
                MAX_ORDER_QUANTITY           ,
                QTY_RCV_TOLERANCE            ,
                OVER_TOLERANCE_ERROR_FLAG    ,
                MARKET_PRICE                 ,
                UNORDERED_FLAG               ,
                CLOSED_FLAG                  ,
                USER_HOLD_FLAG               ,
                CANCEL_FLAG                  ,
                CANCELLED_BY                 ,
                CANCEL_DATE                  ,
                CANCEL_REASON                ,
                FIRM_STATUS_LOOKUP_CODE      ,
                FIRM_DATE                    ,
                VENDOR_PRODUCT_NUM           ,
                CONTRACT_NUM                 ,
                TAXABLE_FLAG                 ,
                TAX_NAME                     ,
                TYPE_1099                    ,
                CAPITAL_EXPENSE_FLAG         ,
                NEGOTIATED_BY_PREPARER_FLAG  ,
                ATTRIBUTE_CATEGORY           ,
                ATTRIBUTE1                   ,
                ATTRIBUTE2                   ,
                ATTRIBUTE3                   ,
                ATTRIBUTE4                   ,
                ATTRIBUTE5                   ,
                ATTRIBUTE6                   ,
                ATTRIBUTE7                   ,
                ATTRIBUTE8                   ,
                ATTRIBUTE9                   ,
                ATTRIBUTE10                  ,
                REFERENCE_NUM                ,
                ATTRIBUTE11                  ,
                ATTRIBUTE12                  ,
                ATTRIBUTE13                  ,
                ATTRIBUTE14                  ,
                ATTRIBUTE15                  ,
                MIN_RELEASE_AMOUNT           ,
                PRICE_TYPE_LOOKUP_CODE       ,
                CLOSED_CODE                  ,
                PRICE_BREAK_LOOKUP_CODE      ,
                USSGL_TRANSACTION_CODE       ,
                GOVERNMENT_CONTEXT           ,
                REQUEST_ID                   ,
                PROGRAM_APPLICATION_ID       ,
                PROGRAM_ID                   ,
                PROGRAM_UPDATE_DATE          ,
                CLOSED_DATE                  ,
                CLOSED_REASON                ,
                CLOSED_BY                    ,
                TRANSACTION_REASON_CODE      ,
                ORG_ID                       ,
                QC_GRADE                     ,
                BASE_UOM                     ,
                BASE_QTY                     ,
                SECONDARY_UOM                ,
                SECONDARY_QTY                ,
                GLOBAL_ATTRIBUTE_CATEGORY    ,
                GLOBAL_ATTRIBUTE1            ,
                GLOBAL_ATTRIBUTE2            ,
                GLOBAL_ATTRIBUTE3            ,
                GLOBAL_ATTRIBUTE4            ,
                GLOBAL_ATTRIBUTE5            ,
                GLOBAL_ATTRIBUTE6            ,
                GLOBAL_ATTRIBUTE7            ,
                GLOBAL_ATTRIBUTE8            ,
                GLOBAL_ATTRIBUTE9            ,
                GLOBAL_ATTRIBUTE10           ,
                GLOBAL_ATTRIBUTE11           ,
                GLOBAL_ATTRIBUTE12           ,
                GLOBAL_ATTRIBUTE13           ,
                GLOBAL_ATTRIBUTE14           ,
                GLOBAL_ATTRIBUTE15           ,
                GLOBAL_ATTRIBUTE16           ,
                GLOBAL_ATTRIBUTE17           ,
                GLOBAL_ATTRIBUTE18           ,
                GLOBAL_ATTRIBUTE19           ,
                GLOBAL_ATTRIBUTE20           ,
                LINE_REFERENCE_NUM           ,
                PROJECT_ID                   ,
                TASK_ID                      ,
                EXPIRATION_DATE              ,
                TAX_CODE_ID                  ,
                OKE_CONTRACT_HEADER_ID       ,
                OKE_CONTRACT_VERSION_ID      ,
                SECONDARY_QUANTITY           ,
                SECONDARY_UNIT_OF_MEASURE    ,
                PREFERRED_GRADE              ,
                AUCTION_HEADER_ID            ,
                AUCTION_DISPLAY_NUMBER       ,
                AUCTION_LINE_NUMBER          ,
                BID_NUMBER                   ,
                BID_LINE_NUMBER              ,
                RETROACTIVE_DATE             
       )
       values
       (
                l_po_line_id                     ,
                cur.LAST_UPDATE_DATE             ,
                CUR.LAST_UPDATED_BY,
                CAPPOIMP_MAPPING.GetPOHeaderIDMapping(cur.PO_HEADER_ID),
                cur.LINE_TYPE_ID                 ,
                cur.LINE_NUM                     ,
                cur.LAST_UPDATE_LOGIN            ,
                cur.CREATION_DATE                ,
                CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
                l_item_id                        ,
                cur.ITEM_REVISION                ,
                cur.CATEGORY_ID                  ,
                cur.ITEM_DESCRIPTION             ,
                cur.UNIT_MEAS_LOOKUP_CODE        ,
                cur.QUANTITY_COMMITTED           ,
                cur.COMMITTED_AMOUNT             ,
                cur.ALLOW_PRICE_OVERRIDE_FLAG    ,
                cur.NOT_TO_EXCEED_PRICE          ,
                cur.LIST_PRICE_PER_UNIT          ,
                cur.UNIT_PRICE                   ,
                cur.QUANTITY                     ,
                cur.UN_NUMBER_ID                 ,
                cur.HAZARD_CLASS_ID              ,
                cur.NOTE_TO_VENDOR               ,
                cur.FROM_HEADER_ID               ,
                cur.FROM_LINE_ID                 ,
                cur.MIN_ORDER_QUANTITY           ,
                cur.MAX_ORDER_QUANTITY           ,
                cur.QTY_RCV_TOLERANCE            ,
                cur.OVER_TOLERANCE_ERROR_FLAG    ,
                cur.MARKET_PRICE                 ,
                cur.UNORDERED_FLAG               ,
                cur.CLOSED_FLAG                  ,
                cur.USER_HOLD_FLAG               ,
                cur.CANCEL_FLAG                  ,
                cur.CANCELLED_BY                 ,
                cur.CANCEL_DATE                  ,
                cur.CANCEL_REASON                ,
                cur.FIRM_STATUS_LOOKUP_CODE      ,
                cur.FIRM_DATE                    ,
                cur.VENDOR_PRODUCT_NUM           ,
                cur.CONTRACT_NUM                 ,
                cur.TAXABLE_FLAG                 ,
                cur.TAX_NAME                     ,
                cur.TYPE_1099                    ,
                cur.CAPITAL_EXPENSE_FLAG         ,
                cur.NEGOTIATED_BY_PREPARER_FLAG  ,
                cur.ATTRIBUTE_CATEGORY           ,
                cur.ATTRIBUTE1                   ,
                cur.ATTRIBUTE2                   ,
                cur.ATTRIBUTE3                   ,
                cur.ATTRIBUTE4                   ,
                cur.ATTRIBUTE5                   ,
                cur.ATTRIBUTE6                   ,
                cur.ATTRIBUTE7                   ,
                cur.ATTRIBUTE8                   ,
                cur.ATTRIBUTE9                   ,
                cur.ATTRIBUTE10                  ,
                cur.REFERENCE_NUM                ,
                cur.ATTRIBUTE11                  ,
                cur.ATTRIBUTE12                  ,
                cur.ATTRIBUTE13                  ,
                cur.ATTRIBUTE14                  ,
                cur.ATTRIBUTE15                  ,
                cur.MIN_RELEASE_AMOUNT           ,
                cur.PRICE_TYPE_LOOKUP_CODE       ,
                cur.CLOSED_CODE                  ,
                cur.PRICE_BREAK_LOOKUP_CODE      ,
                cur.USSGL_TRANSACTION_CODE       ,
                cur.GOVERNMENT_CONTEXT           ,
                cur.REQUEST_ID                   ,
                cur.PROGRAM_APPLICATION_ID       ,
                cur.PROGRAM_ID                   ,
                cur.PROGRAM_UPDATE_DATE          ,
                cur.CLOSED_DATE                  ,
                cur.CLOSED_REASON                ,
                cur.CLOSED_BY                    ,
                cur.TRANSACTION_REASON_CODE      ,
                CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
                cur.QC_GRADE                     ,
                cur.BASE_UOM                     ,
                cur.BASE_QTY                     ,
                cur.SECONDARY_UOM                ,
                cur.SECONDARY_QTY                ,
                cur.GLOBAL_ATTRIBUTE_CATEGORY    ,
                cur.GLOBAL_ATTRIBUTE1            ,
                cur.GLOBAL_ATTRIBUTE2            ,
                cur.GLOBAL_ATTRIBUTE3            ,
                cur.GLOBAL_ATTRIBUTE4            ,
                cur.GLOBAL_ATTRIBUTE5            ,
                cur.GLOBAL_ATTRIBUTE6            ,
                cur.GLOBAL_ATTRIBUTE7            ,
                cur.GLOBAL_ATTRIBUTE8            ,
                cur.GLOBAL_ATTRIBUTE9            ,
                cur.GLOBAL_ATTRIBUTE10           ,
                cur.GLOBAL_ATTRIBUTE11           ,
                cur.GLOBAL_ATTRIBUTE12           ,
                cur.GLOBAL_ATTRIBUTE13           ,
                cur.GLOBAL_ATTRIBUTE14           ,
                cur.GLOBAL_ATTRIBUTE15           ,
                cur.GLOBAL_ATTRIBUTE16           ,
                cur.GLOBAL_ATTRIBUTE17           ,
                cur.GLOBAL_ATTRIBUTE18           ,
                cur.GLOBAL_ATTRIBUTE19           ,
                cur.GLOBAL_ATTRIBUTE20           ,
                cur.LINE_REFERENCE_NUM           ,
                cur.PROJECT_ID                   ,
                cur.TASK_ID                      ,
                cur.EXPIRATION_DATE              ,
                cur.TAX_CODE_ID                  ,
                cur.OKE_CONTRACT_HEADER_ID       ,
                cur.OKE_CONTRACT_VERSION_ID      ,
                cur.SECONDARY_QUANTITY           ,
                cur.SECONDARY_UNIT_OF_MEASURE    ,
                cur.PREFERRED_GRADE              ,
                cur.AUCTION_HEADER_ID            ,
                cur.AUCTION_DISPLAY_NUMBER       ,
                cur.AUCTION_LINE_NUMBER          ,
                cur.BID_NUMBER                   ,
                cur.BID_LINE_NUMBER              ,
                cur.RETROACTIVE_DATE              
       );       
	 exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_PURCHASE_ORDER_LINE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
